package nl.saxion.dbi.api;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nl.saxion.dbi.dao.BazenbaseDao;
import nl.saxion.dbi.exception.DatabaseDoesNotExistsException;
import nl.saxion.dbi.exception.FieldNotFoundException;
import nl.saxion.dbi.exception.KeyNotFoundException;
import nl.saxion.dbi.model.Database;
import nl.saxion.dbi.model.Key;
import nl.saxion.dbi.model.KeyPair;
import nl.saxion.dbi.model.KeyVeld;
import nl.saxion.dbi.model.Row;
import nl.saxion.dbi.model.StringBasedPropertyAble;
import nl.saxion.dbi.model.Tabel;
import nl.saxion.dbi.model.Veld;

public class BazenbaseUtility
{

	List<Database> databases;

	BazenbaseDao dao = BazenbaseDao.getInstance();

	private static BazenbaseUtility instance = new BazenbaseUtility();

	public static BazenbaseUtility getInstance()
	{
		return instance;
	}

	private BazenbaseUtility()
	{
		this.clean();
	}

	public void clean()
	{
		databases = dao.getDatabases();
		if (databases == null)
		{
			throw new RuntimeException("Databases where null!!!");
		}
	}

	public Database getDatabaseByName(String naam)
	{
		for (Database database : databases)
		{
			if (database.getNaam().equals(naam))
			{
				return database;
			}
		}
		return null;
	}

	public Database getDatabaseByTabel(Tabel tabel)
	{
		for (Database database : databases)
		{
			if (database.getTabellen().contains(tabel))
			{
				return database;
			}
		}
		return null;
	}

	public Tabel getTabelByName(String databaseNaam, String naam) throws DatabaseDoesNotExistsException
	{
		Database database = getDatabaseByName(databaseNaam);
		if (database != null)
		{
			for (Tabel tabel : database.getTabellen())
			{
				if (tabel.getNaam().equals(naam))
				{
					return tabel;
				}
			}
		}
		else
		{
			throw new DatabaseDoesNotExistsException("Database bestaat niet");
		}
		return null;
	}

	public List<Key> getUniqueKeys(String database, String type) throws DatabaseDoesNotExistsException
	{
		List<Key> result = new ArrayList<>();
		Tabel tabel = getTabelByName(database, type);

		if (tabel != null)
		{
			for (Key key : tabel.getKeys())
			{
				if (key.isUnique())
				{
					result.add(key);
				}
			}
			return result;

		}
		throw new IllegalStateException("gegeven type bestaat niet");
	}

	public List<KeyPair> getForeignToKeysForVeld(String databaseNaam, String tabelNaam, String veldNaam)
		throws DatabaseDoesNotExistsException
	{
		Database database = getDatabaseByName(databaseNaam);
		Tabel tabel = getTabelByName(databaseNaam, tabelNaam);
		List<KeyPair> result = new ArrayList<>();

		for (KeyPair keyPair : database.getKeyPairs())
		{
			for (Key key : tabel.getKeys())
			{
				for (KeyVeld keyVeld : key.getVelden())
				{
					if (keyPair.getToKey().getId() == key.getId() && keyVeld.getVeld().getNaam().equals(veldNaam))
					{
						result.add(keyPair);
					}
				}
			}
		}

		return result;
	}

	public void addDatabase(String naam)
	{
		Database database = dao.insertDatabase(naam);
		System.out.println("Database: " + database);
		if (database != null)
		{
			databases.add(database);
		}
	}

	public List<Database> getDatabases()
	{
		return databases;
	}

	public List<Tabel> getTabellen(String naam)
	{
		Database database = getDatabaseByName(naam);
		if (database != null)
		{
			return database.getTabellen();
		}
		return null;
	}

	public boolean isUnique(List<Key> uniqueKeys, StringBasedPropertyAble existingObject,
		Map<String, Object> propertyMap)
	{
		boolean result = true;
		for (Key key : uniqueKeys)
		{
			boolean isUnique = false;
			for (KeyVeld keyVeld : key.getVelden())
			{
				Object existingProperty = existingObject.getProperty(keyVeld.getVeld().getNaam());
				Object newProperty = propertyMap.get(keyVeld.getVeld().getNaam());
				if (!nullSafeEqualsMethod(existingProperty, newProperty))
				{
					isUnique = true;
				}
			}

			result = !isUnique ? false : result;
		}
		return result;
	}

	public boolean nullSafeEqualsMethod(Object object1, Object object2)
	{
		if (object1 == null && object2 == null)
		{
			return true;
		}
		if (object1 != null && object1.equals(object2))
		{
			return true;
		}
		return false;
	}

	public void addTable(String databaseNaam, String naam)
	{
		Database database = getDatabaseByName(databaseNaam);
		Tabel tabel = dao.insertTabel(database, naam);
		System.out.println("Tabel: " + tabel);
		if (tabel != null)
		{
			database.getTabellen().add(tabel);
		}
	}

	public boolean isToKey(String databaseNaam, Key key)
	{
		Database database = getDatabaseByName(databaseNaam);
		for (KeyPair keyPair : database.getKeyPairs())
		{
			if (keyPair.getToKey().getId() == key.getId())
			{
				return true;
			}
		}
		return false;
	}

	public void deleteKey(Key key, String databaseNaam)
	{
		deleteKeyFromDatabase(key);
		deleteKeyFromModel(key, databaseNaam);
	}

	private void deleteKeyFromDatabase(Key key)
	{
		dao.deleteKeyPair(key.getId());
		dao.deleteKeyVelden(key.getId());
		dao.deletePrimaryKey(key.getId());
		dao.deleteKey(key.getId());
	}

	private void deleteKeyFromModel(Key key, String databaseNaam)
	{
		deleteKeyPairFromModel(key, databaseNaam);
		deleteKeyFromTabel(key, databaseNaam);
	}

	private void deleteKeyPairFromModel(Key key, String databaseNaam)
	{
		Database database = getDatabaseByName(databaseNaam);
		List<KeyPair> iteratingList = new ArrayList<>(database.getKeyPairs());
		for (KeyPair keyPair : iteratingList)
		{
			if (keyPair.getFromKey().getId() == key.getId())
			{
				database.getKeyPairs().remove(keyPair);
			}
		}
	}

	private void deleteKeyFromTabel(Key key, String databaseNaam)
	{
		Database database = getDatabaseByName(databaseNaam);
		for (Tabel tabel : database.getTabellen())
		{
			List<Key> iteratingList = new ArrayList<>(tabel.getKeys());
			for (Key tabelKey : iteratingList)
			{
				if (tabelKey.getId() == key.getId())
				{
					tabel.getKeys().remove(tabelKey);
				}
			}
			if (tabel.getPrimaryKey() != null && tabel.getPrimaryKey().getId() == key.getId())
			{
				tabel.setPrimaryKey(null);
			}
		}
	}

	public void addVeld(Tabel tabel, String naam, String type, boolean notNull)
	{
		Veld veld = dao.addVeld(tabel, naam, type, notNull);
		System.out.println("Veld: " + veld);
		if (veld != null)
		{
			tabel.getVelden().add(veld);
		}
	}

	public Tabel getTabelByVeld(Veld sourceVeld)
	{
		for (Database database : databases)
		{
			for (Tabel tabel : database.getTabellen())
			{
				for (Veld veld : tabel.getVelden())
				{
					if (veld.getId() == sourceVeld.getId())
					{
						return tabel;
					}
				}
			}
		}
		return null;
	}

	public List<Key> getAllKeys()
	{
		List<Key> result = new ArrayList<>();
		for (Database database : databases)
		{
			for (Tabel tabel : database.getTabellen())
			{
				result.addAll(tabel.getKeys());
			}
		}
		return result;
	}

	public void addKey(Tabel tabel, String keyNaam, boolean isUnique, String... veldnaam) throws FieldNotFoundException
	{
		Key key = dao.addKey(keyNaam, isUnique);

		int volgnummer = 0;

		for (String keyVeldNaam : veldnaam)
		{
			boolean found = false;
			for (Veld veld : tabel.getVelden())
			{
				if (veld.getNaam().equals(keyVeldNaam))
				{
					KeyVeld keyVeld = dao.addKeyVeld(veld, key, volgnummer);
					key.getVelden().add(keyVeld);
					volgnummer++;
					found = true;
				}
			}
			if (!found)
			{
				throw new FieldNotFoundException(keyVeldNaam);
			}
		}
		tabel.getKeys().add(key);
		System.out.println("Key " + key);
	}

	public void setPrimaryKey(Tabel tabel, String keyNaam) throws KeyNotFoundException
	{
		Key oudPrimaryKey = tabel.getPrimaryKey();
		if (oudPrimaryKey != null)
		{
			dao.deletePrimaryKey(oudPrimaryKey.getId());
			tabel.setPrimaryKey(null);
		}
		boolean found = false;
		for (Key key : tabel.getKeys())
		{
			if (key.getNaam().equals(keyNaam))
			{
				dao.setPrimaryKey(tabel, key);
				tabel.setPrimaryKey(key);
				found = true;
				System.out.println("Tabel " + tabel);
			}
		}
		if (!found)
		{
			throw new KeyNotFoundException(keyNaam);
		}
	}

	public Key getKeyByName(String databaseNaam, String keyNaam)
	{
		Database database = getDatabaseByName(databaseNaam);
		for (Tabel tabel : database.getTabellen())
		{
			for (Key key : tabel.getKeys())
			{
				if (key.getNaam().equals(keyNaam))
				{
					return key;
				}
			}
		}
		return null;
	}

	public void addForeignKey(Database database, Key fromKey, Key toKey)
	{
		KeyPair keyPair = dao.insertKeyPair(fromKey, toKey);
		System.out.println("KeyPair: " + keyPair);
		if (keyPair != null)
		{
			database.getKeyPairs().add(keyPair);
		}
	}

	public void deleteVeld(Database database, Tabel tabel, String veldNaam)
	{
		List<Veld> velden = new ArrayList<>(tabel.getVelden());
		for (Veld veld : velden)
		{
			if (veld.getNaam().equals(veldNaam))
			{
				deleteVeldFromDatabase(veld);
				deleteVeldFromModel(veld);
			}
		}

	}

	private void deleteVeldFromDatabase(Veld veld)
	{
		dao.deleteVeld(veld);
	}

	private void deleteVeldFromModel(Veld veld)
	{
		Tabel tabel = getTabelByVeld(veld);
		tabel.getVelden().remove(veld);
	}

	public void deleteTabel(Database database, Tabel tabel)
	{
		dao.deleteTabel(tabel);
		database.getTabellen().remove(tabel);
	}

	public void deleteDatabase(Database database)
	{
		List<Tabel> tabellen = new ArrayList<>(database.getTabellen());
		for (Tabel tabel : tabellen)
		{
			List<Key> keys = new ArrayList<>(tabel.getKeys());
			for (Key key : keys)
			{
				deleteKey(key, database.getNaam());
			}
			List<Veld> velden = new ArrayList<>(tabel.getVelden());
			for (Veld veld : velden)
			{
				deleteVeld(database, tabel, veld.getNaam());
			}
			deleteTabel(database, tabel);
		}
		dao.deleteDatabase(database);
		databases.remove(database);
	}

	public List<KeyPair> getFromKeys(Database database, Tabel tabel)
	{
		List<KeyPair> result = new ArrayList<>();
		for (KeyPair keyPair : database.getKeyPairs())
		{
			boolean keyPairFound = false;
			for (KeyVeld keyVeld : keyPair.getFromKey().getVelden())
			{
				for (Veld veld : tabel.getVelden())
				{
					if (keyVeld.getVeld().getId() == veld.getId())
					{
						result.add(keyPair);
						keyPairFound = true;
						break;
					}
				}
				if (keyPairFound)
				{
					break;
				}

			}
		}
		return result;
	}

	public String getDataByKey(Tabel tabel, Key fromKey, String[] data, int volgnummer)
	{
		KeyVeld keyVeld = fromKey.getVelden().get(volgnummer);

		for (int i = 0; i < tabel.getVelden().size(); i++)
		{
			if (tabel.getVelden().get(i).getId() == keyVeld.getVeld().getId())
			{
				return data[i];
			}
		}
		return null;
	}

	public Map<String, String> buildForeignFromKeyPropertyMap(Database database, Tabel tabel, KeyPair keyPair,
		String[] data)
	{
		Map<String, String> propertyMap = new HashMap<>();

		for (int i = 0; i < keyPair.getFromKey().getVelden().size(); i++)
		{
			String toVeldNaam = keyPair.getToKey().getVelden().get(i).getVeld().getNaam();
			String value = getDataByKey(tabel, keyPair.getFromKey(), data, i);
			propertyMap.put(toVeldNaam, value);
		}
		return propertyMap;
	}

	public Map<String, String> buildForeignToKeyPropertyMap(Database database, Tabel tabel, KeyPair keyPair,
		String[] data)
	{
		Map<String, String> propertyMap = new HashMap<>();

		for (int i = 0; i < keyPair.getToKey().getVelden().size(); i++)
		{
			String fromVeldNaam = keyPair.getFromKey().getVelden().get(i).getVeld().getNaam();
			String value = getDataByKey(tabel, keyPair.getToKey(), data, i);
			propertyMap.put(fromVeldNaam, value);
		}
		return propertyMap;
	}

	public void addRow(Tabel tabel, Row row)
	{
		tabel.addRow(row);
		System.out.println("Data succesvol toegevoegd");
	}

	public boolean isForeignKeyConstraintValid(Map<String, String> propertyMap, Key key)
	{
		Tabel tabel = getTabelByVeld(key.getVelden().get(0).getVeld());

		for (Row row : tabel.getData())
		{
			if (isRowConstraintValid(row, propertyMap))
			{
				return true;
			}
		}
		return false;
	}

	public boolean isForeignKeyConstraintNotValid(Map<String, String> propertyMap, Key key)
	{
		Tabel tabel = getTabelByVeld(key.getVelden().get(0).getVeld());

		for (Row row : tabel.getData())
		{
			if (isRowConstraintValid(row, propertyMap))
			{
				return false;
			}
		}
		return true;

	}

	private boolean isRowConstraintValid(Row row, Map<String, String> propertyMap)
	{
		for (String veldNaam : propertyMap.keySet())
		{
			if (!propertyMap.get(veldNaam).equals(row.getProperty(veldNaam)))
			{
				return false;
			}
		}
		return true;
	}

	public Veld getVeldByNaam(Tabel tabel, String veldNaam)
	{
		Veld veld = null;
		for (Veld searchVeld : tabel.getVelden())
		{
			if (searchVeld.getNaam().equals(veldNaam))
			{
				veld = searchVeld;
				break;
			}
		}
		return veld;

	}

	public List<KeyPair> getToKeys(Database database, Tabel tabel)
	{
		List<KeyPair> result = new ArrayList<>();

		for (KeyPair keyPair : database.getKeyPairs())
		{
			boolean keyPairFound = false;
			for (KeyVeld keyVeld : keyPair.getToKey().getVelden())
			{
				for (Veld veld : tabel.getVelden())
				{
					if (keyVeld.getVeld().getId() == veld.getId())
					{
						result.add(keyPair);
						keyPairFound = true;
						break;
					}
				}
				if (keyPairFound)
				{
					break;
				}
			}
		}
		return result;
	}
}
